﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
using System.Data;


namespace DAL
{
    public class HouseEvaluationService
    {
        public static HouseEvaluation AddHouseEvaluation(HouseEvaluation evaluation)
        {
            string sql = string.Format("insert into HouseEvaluation(HouseId,ClientId,PublishTime,Content,Level) values("
                                + "{0}, {1}, N'{2}', N'{3}', {4});select @@IDENTITY;", 
                                evaluation.HouseId, evaluation.ClientId, evaluation.PublishTime,
                                evaluation.Content, evaluation.Level);
            int id = DBHelper.GetScalar(sql);
            return GetHouseEvaluationById(id);
        }

       

        public static HouseEvaluation GetHouseEvaluationById(int id)
        {
            string sql = string.Format("select * from HouseEvaluation where Id={0}", id);
            return GetHouseEvaluationBySql(sql);
        }

        private static HouseEvaluation GetHouseEvaluationBySql(string sql)
        {
            List<HouseEvaluation> list = GetHouseEvaluationsBySql(sql);
            if (list.Count() > 0)
                return list[0];
            return null;
        }

        public static List<HouseEvaluation> GetAllHouseEvaluations(int houseId)
        {
            string sql = string.Format("select * from HouseEvaluation where HouseId={0}", houseId);
            return GetHouseEvaluationsBySql(sql);
        }

        private static List<HouseEvaluation> GetHouseEvaluationsBySql(string sql)
        {
            List<HouseEvaluation> list = new List<HouseEvaluation>();
            DataTable dt = DBHelper.GetDataSet(sql);
            foreach (DataRow row in dt.Rows)
            {
                HouseEvaluation evaluation = new HouseEvaluation();
                evaluation.Id = (int)row["Id"];
                evaluation.HouseId = (int)row["HouseId"];
                evaluation.ClientId = (int)row["ClientId"];
                evaluation.PublishTime = DateTime.Parse(row["PublishTime"].ToString());
                evaluation.Content = (string)row["Content"];
                evaluation.Level = (int)row["Level"];
                list.Add(evaluation);
            }
            return list;
        }

        public static List<HouseEvaluation> GetHouseEvaluationsByUserId(int userId)
        {
            string sql = string.Format("select * from HouseEvaluation where HouseId in (select Id from House where HouseHolderId={0}) order by PublishTime desc", userId);
            return GetHouseEvaluationsBySql(sql);
        }

        public static int Count()
        {
            string sql = string.Format("select count(*) from HouseEvaluation");
            return DBHelper.GetScalar(sql);
        }
    }
}
